using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Collections;

namespace BESMusicShop
{
    class DatabaseFunctions
    {
        static SqlConnection connection;
        static string sConStr;

        public DatabaseFunctions()
        {
        }

        public static void MakeConnection()
        {
            sConStr = "Data Source=(local);User ID=BoTi;Password=botond;Initial Catalog=BESMusicShop";
            connection = new SqlConnection(sConStr);
            connection.Open();
        }

        public static void CloseConnection()
        {
            connection.Close();
        }

        public static Boolean isAdminLoginValid(string adminText, string adminPass)
        {
            Boolean valid = false;

            MakeConnection();
            string sSQL = "SELECT * FROM AdminTable WHERE Name LIKE '" + adminText + "' AND Password LIKE '" + adminPass + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL,connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AdminTable");

            if (dataSet.Tables["AdminTable"].Rows.Count != 0)
            {
                valid = true;
            }
            CloseConnection();
            return valid;
        }
        //USERTABLE----------
        public static void fillUsersDataGridView(DataGridView usersDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT * FROM UserTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL,connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "UserTable");
            usersDataGridView.DataSource = dataSet.Tables["UserTable"];
            CloseConnection();
        }

        public static void deleteUsers(DataGridView usersDataGridView, String id)
        {
            MakeConnection();
            string sSQL = "DELETE FROM UserTable WHERE UserID = '" + id + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "UserTable");
            CloseConnection();
        }

        public static int getUserMax()
        {
            int max;
            MakeConnection();
            string sSQL = "SELECT MAX(UserID) FROM UserTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            max = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return max;
        }

        public static void insertUsers(int id, string name, string password, string location, string email, string gender, int age)
        {
            MakeConnection();

            SqlCommand command = new SqlCommand("insertUser", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@UserID", id));
            command.Parameters.Add(new SqlParameter("@UserName", name));
            command.Parameters.Add(new SqlParameter("@Password", password));
            command.Parameters.Add(new SqlParameter("@Location",location));
            command.Parameters.Add(new SqlParameter("@Email",email));
            command.Parameters.Add(new SqlParameter("@Gender",gender));
            command.Parameters.Add(new SqlParameter("@Age",age));

            SqlDataReader reader = command.ExecuteReader();
        }

        public static void editUsers(DataGridView usersDataGridView, int id, string name, string password, string location, string email, string gender, int age)
        {
            MakeConnection();
            SqlCommand command = new SqlCommand("updateUser", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@UserID", id));
            command.Parameters.Add(new SqlParameter("@UserName", name));
            command.Parameters.Add(new SqlParameter("@Password", password));
            command.Parameters.Add(new SqlParameter("@Location", location));
            command.Parameters.Add(new SqlParameter("@Email", email));
            command.Parameters.Add(new SqlParameter("@Gender", gender));
            command.Parameters.Add(new SqlParameter("@Age", age));

            SqlDataReader reader = command.ExecuteReader();
        }
 
        //ADMINTABLE-------------
        public static void fillAdminsDataGridView(DataGridView usersDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT * FROM AdminTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AdminTable");
            usersDataGridView.DataSource = dataSet.Tables["AdminTable"];
            CloseConnection();
        }

        public static void deleteAdmins(DataGridView adminsDataGridView, String id)
        {
            MakeConnection();
            string sSQL = "DELETE FROM AdminTable WHERE AdminID = '" + id + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AdminTable");
            CloseConnection();
        }

        public static int getAdminMax()
        {
            int max;
            MakeConnection();
            string sSQL = "SELECT MAX(AdminID) FROM AdminTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            max = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return max;
        }

        public static void insertAdmins(int id, string name, string password)
        {
            MakeConnection();

            SqlCommand command = new SqlCommand("insertAdmin", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@AdminID", id));
            command.Parameters.Add(new SqlParameter("@Name", name));
            command.Parameters.Add(new SqlParameter("@Password", password));

            SqlDataReader reader = command.ExecuteReader();
        }

        public static void editAdmins(DataGridView adminsDataGridView, int id, string name, string password)
        {
            MakeConnection();
            SqlCommand command = new SqlCommand("updateAdmin", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@AdminID", id));
            command.Parameters.Add(new SqlParameter("@Name", name));
            command.Parameters.Add(new SqlParameter("@Password", password));

            SqlDataReader reader = command.ExecuteReader();
        }
        //----------------------

        //ALBUMTABLE-------------
        public static void fillAlbumsDataGridView(DataGridView usersDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT AlbumTable.AlbumID, AlbumTable.Title, AlbumTable.Type, AlbumTable.Description, LabelTable.Name AS Label FROM AlbumTable, LabelTable WHERE AlbumTable.LabelID = LabelTable.LabelID";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AlbumTable");
            usersDataGridView.DataSource = dataSet.Tables["AlbumTable"];
            CloseConnection();
        }

        public static void deleteAlbums(DataGridView albumsDataGridView, String id)
        {
            MakeConnection();
            string sSQL = "DELETE FROM AlbumTable WHERE AlbumID = '" + id + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AlbumTable");
            CloseConnection();
        }

        public static int getAlbumMax()
        {
            int max;
            MakeConnection();
            string sSQL = "SELECT MAX(AlbumID) FROM AlbumTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            max = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return max;
        }

        public static int getLabelSelectedTextID(string selectedText)
        {
            int index;
            MakeConnection();
            string sSQL = "SELECT LabelID FROM LabelTable WHERE Name = '" + selectedText + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            index = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return index;
        }

        public static string[] fillLabelComboBox()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT Name FROM LabelTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            string[] labelList = new string[count];

            for (int i = 0; i < count; i++)
            {
                labelList[i] = Convert.ToString(dataSet.Tables["Table"].Rows[i][0]);
            }

            return labelList;
        }

        public static ArrayList fillLabelArray()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT Name FROM LabelTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            ArrayList arrayList = new ArrayList();

            for (int i = 0; i < count; i++)
            {
                arrayList.Add(Convert.ToString(dataSet.Tables["Table"].Rows[i][0]));
            }

            return arrayList;
        }

        public static void insertAlbums(int id, string title, string type, string description, int labelID)
        {
            MakeConnection();

            SqlCommand command = new SqlCommand("insertAlbum", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@AlbumID", id));
            command.Parameters.Add(new SqlParameter("@Title", title));
            command.Parameters.Add(new SqlParameter("@Type", type));
            command.Parameters.Add(new SqlParameter("@Description", description));
            command.Parameters.Add(new SqlParameter("@LabelID", labelID));

            SqlDataReader reader = command.ExecuteReader();

        }

        public static void editAlbums(DataGridView albumsDataGridView, int id, string title, string type, string description, int labelID)
        {
            MakeConnection();

            SqlCommand command = new SqlCommand("updateAlbum", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@AlbumID", id));
            command.Parameters.Add(new SqlParameter("@Title", title));
            command.Parameters.Add(new SqlParameter("@Type", type));
            command.Parameters.Add(new SqlParameter("@Description", description));
            command.Parameters.Add(new SqlParameter("@LabelID", labelID));

            SqlDataReader reader = command.ExecuteReader();
        }
        //----------------------

        //LABELTABLE-------------
        public static void fillLabelsDataGridView(DataGridView usersDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT * FROM LabelTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "LabelTable");
            usersDataGridView.DataSource = dataSet.Tables["LabelTable"];
            CloseConnection();
        }

        public static void deleteLabels(DataGridView labelsDataGridView, String id)
        {
            MakeConnection();
            string sSQL = "DELETE FROM LabelTable WHERE LabelID = '" + id + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "LabelTable");
            CloseConnection();
        }

        public static int getLabelMax()
        {
            int max;
            MakeConnection();
            string sSQL = "SELECT MAX(LabelID) FROM LabelTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            max = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return max;
        }

      
        public static void insertLabels(int id, string name, string description)
        {
            MakeConnection();
            string query = @"SELECT * FROM LabelTable";
            string update = @"INSERT INTO LabelTable(LabelID,Name,Description)values(@id,@name,@description)";
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "LabelTable");
            DataTable dataTable = dataSet.Tables["LabelTable"];

            DataRow dataRow = dataTable.NewRow();
            dataRow["LabelID"] = id;
            dataRow["Name"] = name;
            dataRow["Description"] = description;
            dataTable.Rows.Add(dataRow);

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@id", SqlDbType.Decimal, 9, "LabelID");
            command.Parameters.Add("@name", SqlDbType.VarChar, 50, "Name");
            command.Parameters.Add("@description", SqlDbType.VarChar, 50, "Description");

            dataAdapter.InsertCommand = command;
            dataAdapter.Update(dataSet, "LabelTable");
        }

        public static void editLabels(DataGridView adminsDataGridView, int id, string name, string description)
        {
            MakeConnection();
            string query = @"SELECT * FROM LabelTable";
            string update = @"UPDATE LabelTable SET Name = @name, Description = @description WHERE LabelID = @id";

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "LabelTable");
            DataTable dataTable = dataSet.Tables["LabelTable"];

            int row = Convert.ToInt32(adminsDataGridView.CurrentRow.Index);

            dataTable.Rows[row]["Name"] = name;
            dataTable.Rows[row]["Description"] = description;

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@name", SqlDbType.VarChar, 50, "Name");
            command.Parameters.Add("@description", SqlDbType.VarChar, 50, "Description");
            SqlParameter parameter = command.Parameters.Add("@id", SqlDbType.Decimal, 9, "LabelID");
            parameter.SourceVersion = DataRowVersion.Original;
            dataAdapter.UpdateCommand = command;
            dataAdapter.Update(dataSet, "LabelTable");
        }

        //----------------------

        //TRACKTABLE-------------
        public static void fillTracksDataGridView(DataGridView usersDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT TrackTable.TrackID, AliasTable.Name AS Alias ,TrackTable.Name AS Title, TrackTable.Length, TrackTable.Price, TrackTable.Genre, TrackTable.RemixName AS Version FROM TrackTable, AliasTable WHERE TrackTable.AliasID = AliasTable.AliasID";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "TrackTable");
            usersDataGridView.DataSource = dataSet.Tables["TrackTable"];
            CloseConnection();
        }

        public static void deleteTracks(DataGridView tracksDataGridView, String id)
        {
            MakeConnection();
            string sSQL = "DELETE FROM TrackTable WHERE TrackID = '" + id + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "TrackTable");
            CloseConnection();
        }

        public static int getTrackMax()
        {
            int max;
            MakeConnection();
            string sSQL = "SELECT MAX(TrackID) FROM TrackTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            max = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return max;
        }

        public static int getAliasSelectedTextID(string selectedText)
        {
            int index;
            MakeConnection();
            string sSQL = "SELECT AliasID FROM AliasTable WHERE Name = '" + selectedText + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            index = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return index;
        }

        public static string[] fillAliasComboBox()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT Name FROM AliasTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            string[] aliasList = new string[count];

            for (int i = 0; i < count; i++)
            {
                aliasList[i] = Convert.ToString(dataSet.Tables["Table"].Rows[i][0]);
            }

            return aliasList;
        }

        public static ArrayList fillAliasArray()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT Name FROM AliasTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            ArrayList arrayList = new ArrayList();

            for (int i = 0; i < count; i++)
            {
                arrayList.Add(Convert.ToString(dataSet.Tables["Table"].Rows[i][0]));
            }

            return arrayList;
        }

        public static void insertTracks(int id, int aliasID, string name, int length, int price, string genre, string remixName)
        {
            MakeConnection();
            string query = @"SELECT * FROM TrackTable";
            string update = @"INSERT INTO TrackTable(TrackID,AliasID,Name,Length,Price,Genre,RemixName)values(@id,@aliasID,@name,@length,@price,@genre,@remixName)";
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "TrackTable");
            DataTable dataTable = dataSet.Tables["TrackTable"];

            DataRow dataRow = dataTable.NewRow();
            dataRow["TrackID"] = id;
            dataRow["AliasID"] = aliasID;
            dataRow["Name"] = name;
            dataRow["Length"] = length;
            dataRow["Price"] = price;
            dataRow["Genre"] = genre;
            dataRow["RemixName"] = remixName;
            dataTable.Rows.Add(dataRow);

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@id", SqlDbType.Decimal, 9, "TrackID");
            command.Parameters.Add("@aliasID", SqlDbType.Decimal, 9, "AliasID");
            command.Parameters.Add("@name", SqlDbType.VarChar, 50, "Name");
            command.Parameters.Add("@length", SqlDbType.Decimal, 9, "Length");
            command.Parameters.Add("@price", SqlDbType.Decimal, 9, "Price");
            command.Parameters.Add("@genre", SqlDbType.VarChar, 50, "Genre");
            command.Parameters.Add("@remixName", SqlDbType.VarChar, 50, "RemixName");

            dataAdapter.InsertCommand = command;
            dataAdapter.Update(dataSet, "TrackTable");
        }

        public static void editTracks(DataGridView tracksDataGridView, int id, int aliasID, string name, int length, int price, string genre, string remixName)
        {
            MakeConnection();
            string query = @"SELECT * FROM TrackTable";
            string update = @"UPDATE TrackTable SET AliasID = @aliasID, Name = @name, Length = @length, Price = @price, Genre = @genre, RemixName = @remixName WHERE TrackID = @id";

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "TrackTable");
            DataTable dataTable = dataSet.Tables["TrackTable"];

            int row = Convert.ToInt32(tracksDataGridView.CurrentRow.Index);

            dataTable.Rows[row]["TrackID"] = id;
            dataTable.Rows[row]["AliasID"] = aliasID;
            dataTable.Rows[row]["Name"] = name;
            dataTable.Rows[row]["Length"] = length;
            dataTable.Rows[row]["Price"] = price;
            dataTable.Rows[row]["Genre"] = genre;
            dataTable.Rows[row]["RemixName"] = remixName;

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@aliasID", SqlDbType.Decimal, 9, "AliasID");
            command.Parameters.Add("@name", SqlDbType.VarChar, 50, "Name");
            command.Parameters.Add("@length", SqlDbType.Decimal, 9, "Length");
            command.Parameters.Add("@price", SqlDbType.Decimal, 9, "Price");
            command.Parameters.Add("@genre", SqlDbType.VarChar, 50, "Genre");
            command.Parameters.Add("@remixName", SqlDbType.VarChar, 50, "RemixName");
            SqlParameter parameter = command.Parameters.Add("@id", SqlDbType.Decimal, 9, "TrackID");
            parameter.SourceVersion = DataRowVersion.Original;
            dataAdapter.UpdateCommand = command;
            dataAdapter.Update(dataSet, "TrackTable");
        }

        //----------------------

        //CART------------------
        public static void fillCartDataGridView(DataGridView cartDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT UserTable.UserName, AliasTable.Name AS Alias, TrackTable.Name AS Title, TrackTable.RemixName AS Version, CartTable.DateAdded FROM UserTable, TrackTable, CartTable, AliasTable WHERE CartTable.UserID = UserTable.UserID AND CartTable.TrackID = TrackTable.TrackID AND TrackTable.AliasID = AliasTable.AliasID";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "CartTable");
            cartDataGridView.DataSource = dataSet.Tables["CartTable"];
            CloseConnection();
        }

        public static int getUserSelectedTextID(string selectedUser)
        {
            int index;
            MakeConnection();
            string sSQL = "SELECT UserID FROM UserTable WHERE UserName = '" + selectedUser + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            index = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return index;
        }

        public static int getTrackSelectedTextID(string selectedText)
        {
            int index;
            MakeConnection();
            string sSQL = "SELECT TrackID FROM TrackTable WHERE Name = '" + selectedText + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            index = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return index;
        }

        public static void deleteCart(DataGridView aliasDataGridView, int UserID, int TrackID)
        {
            MakeConnection();
            string sSQL = "DELETE FROM CartTable WHERE UserID = '" + UserID + "' AND TrackID = '" + TrackID + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "CartTable");
            CloseConnection();
        }

        public static string[] fillUserComboBox()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT UserName FROM UserTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            string[] labelList = new string[count];

            for (int i = 0; i < count; i++)
            {
                labelList[i] = Convert.ToString(dataSet.Tables["Table"].Rows[i][0]);
            }

            return labelList;
        }

        public static string[] fillTrackComboBox()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT AliasTable.Name, TrackTable.Name, TrackTable.RemixName FROM TrackTable, AliasTable WHERE TrackTable.AliasID = AliasTable.AliasID";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            string[] labelList = new string[count];

            for (int i = 0; i < count; i++)
            {
                labelList[i] = Convert.ToString(dataSet.Tables["Table"].Rows[i][0]) + " - " + Convert.ToString(dataSet.Tables["Table"].Rows[i][1]) + " (" + Convert.ToString(dataSet.Tables["Table"].Rows[i][2]) + ")";
            }

            return labelList;
        }

        public static void insertCart(int userID, int trackID, DateTime dateAdded)
        {
            MakeConnection();
            string query = @"SELECT * FROM CartTable";
            string update = @"INSERT INTO CartTable(UserID,TrackID,DateAdded)values(@userID,@trackID,@dateAdded)";
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "CartTable");
            DataTable dataTable = dataSet.Tables["CartTable"];

            DataRow dataRow = dataTable.NewRow();
            dataRow["UserID"] = userID;
            dataRow["TrackID"] = trackID;
            dataRow["DateAdded"] = dateAdded;

            dataTable.Rows.Add(dataRow);

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@userID", SqlDbType.Decimal, 9, "UserID");
            command.Parameters.Add("@trackID", SqlDbType.Decimal, 9, "TrackID");
            command.Parameters.Add("@dateAdded", SqlDbType.DateTime, 50, "DateAdded");

            dataAdapter.InsertCommand = command;
            dataAdapter.Update(dataSet, "CartTable");
        }
        //---------------------

        //APPEARSON------------
        public static void fillAppearsOnDataGridView(DataGridView appearsOnDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT AliasTable.Name AS Alias, TrackTable.Name AS Title, TrackTable.RemixName AS Version, AlbumTable.Title AS Album FROM TrackTable, AliasTable, AlbumTable, AppearsOnTable WHERE AppearsOnTable.TrackID = TrackTable.TrackID AND AppearsOnTable.AlbumID = AlbumTable.AlbumID AND TrackTable.AliasID = AliasTable.AliasID";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AppearsOnTable");
            appearsOnDataGridView.DataSource = dataSet.Tables["AppearsOnTable"];
            CloseConnection();
        }

        public static void deleteAppearsOn(DataGridView appearsOnDataGridView, String TrackID, String AlbumID)
        {
            MakeConnection();
            string sSQL = "DELETE FROM AppearsOnTable WHERE TrackID = '" + TrackID + "' AND AlbumID = '" + AlbumID + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AppearsOnTable");
            CloseConnection();
        }

        public static string[] fillAlbumComboBox()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT Title FROM AlbumTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            string[] labelList = new string[count];

            for (int i = 0; i < count; i++)
            {
                labelList[i] = Convert.ToString(dataSet.Tables["Table"].Rows[i][0]);
            }

            return labelList;
        }

        public static int getAlbumSelectedTextID(string selectedText)
        {
            int index;
            MakeConnection();
            string sSQL = "SELECT AlbumID FROM AlbumTable WHERE Title = '" + selectedText + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            index = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return index;
        }


        public static ArrayList fillTrackIDArray()
        {
            ArrayList arrayList = new ArrayList();
            MakeConnection();
            string sSQL = "SELECT TrackID FROM TrackTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            for (int i = 0; i < dataSet.Tables["Table"].Rows.Count; i++)
            {
                arrayList.Add(Convert.ToString(dataSet.Tables["Table"].Rows[i][0]));
            }
            CloseConnection();
            return arrayList;
        }

        public static void insertAppearsOn(int trackID, int albumID)
        {
            MakeConnection();
            string query = @"SELECT * FROM AppearsOnTable";
            string update = @"INSERT INTO AppearsOnTable(TrackID,AlbumID)values(@trackID,@albumID)";
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AppearsOnTable");
            DataTable dataTable = dataSet.Tables["AppearsOnTable"];

            DataRow dataRow = dataTable.NewRow();
            dataRow["TrackID"] = trackID;
            dataRow["AlbumID"] = albumID;

            dataTable.Rows.Add(dataRow);

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@trackID", SqlDbType.Decimal, 9, "TrackID");
            command.Parameters.Add("@albumID", SqlDbType.Decimal, 9, "AlbumID");

            dataAdapter.InsertCommand = command;
            dataAdapter.Update(dataSet, "AppearsOnTable");
        }
        //---------------------

        //ALIAS------------
        public static void fillAliasDataGridView(DataGridView aliasOnDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT AliasTable.AliasID, ArtistTable.RealName AS Name, AliasTable.Name As Alias FROM AliasTable, ArtistTable WHERE AliasTable.ArtistID = ArtistTable.ArtistID";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AliasTable");
            aliasOnDataGridView.DataSource = dataSet.Tables["AliasTable"];
            CloseConnection();
        }

        public static void deleteAlias(DataGridView aliasDataGridView, String id)
        {
            MakeConnection();
            string sSQL = "DELETE FROM AliasTable WHERE AliasID = '" + id + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AliasTable");
            CloseConnection();
        }

        public static int getAliasMax()
        {
            int max;
            MakeConnection();
            string sSQL = "SELECT MAX(AliasID) FROM AliasTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            max = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return max;
        }

        public static string[] fillArtistComboBox()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT RealName FROM ArtistTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            string[] aliasList = new string[count];

            for (int i = 0; i < count; i++)
            {
                aliasList[i] = Convert.ToString(dataSet.Tables["Table"].Rows[i][0]);
            }

            return aliasList;
        }

        public static ArrayList fillArtistArray()
        {
            int count;
            MakeConnection();
            string sSQL = "SELECT RealName FROM ArtistTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");

            count = dataSet.Tables["Table"].Rows.Count;
            ArrayList arrayList = new ArrayList();

            for (int i = 0; i < count; i++)
            {
                arrayList.Add(Convert.ToString(dataSet.Tables["Table"].Rows[i][0]));
            }

            return arrayList;
        }

        public static int getArtistSelectedTextID(string selectedText)
        {
            int index;
            MakeConnection();
            string sSQL = "SELECT ArtistID FROM ArtistTable WHERE RealName = '" + selectedText + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            index = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return index;
        }

        public static void insertAlias(int id, int artistID, string name)
        {
            MakeConnection();
            string query = @"SELECT * FROM AliasTable";
            string update = @"INSERT INTO AliasTable(AliasID,ArtistID,Name)values(@id,@artistID,@name)";
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AliasTable");
            DataTable dataTable = dataSet.Tables["AliasTable"];

            DataRow dataRow = dataTable.NewRow();
            dataRow["AliasID"] = id;
            dataRow["ArtistID"] = artistID;
            dataRow["Name"] = name;
            dataTable.Rows.Add(dataRow);

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@id", SqlDbType.Decimal, 9, "AliasID");
            command.Parameters.Add("@artistID", SqlDbType.Decimal, 9, "ArtistID");
            command.Parameters.Add("@name", SqlDbType.VarChar, 50, "Name");

            dataAdapter.InsertCommand = command;
            dataAdapter.Update(dataSet, "AliasTable");
        }

        public static void editAlias(DataGridView aliasDataGridView, int aliasID, int artistID, string name)
        {
            MakeConnection();
            string query = @"SELECT * FROM AliasTable";
            string update = @"UPDATE AliasTable SET ArtistID = @artistID, Name = @name WHERE AliasID = @aliasID";

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "AliasTable");
            DataTable dataTable = dataSet.Tables["AliasTable"];

            int row = Convert.ToInt32(aliasDataGridView.CurrentRow.Index);

            dataTable.Rows[row]["ArtistID"] = artistID;
            dataTable.Rows[row]["Name"] = name;


            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@name", SqlDbType.VarChar, 50, "Name");
            command.Parameters.Add("@artistID", SqlDbType.Decimal, 9, "ArtistID");
            SqlParameter parameter = command.Parameters.Add("@aliasID", SqlDbType.Decimal, 9, "AliasID");
            parameter.SourceVersion = DataRowVersion.Original;
            dataAdapter.UpdateCommand = command;
            dataAdapter.Update(dataSet, "AliasTable");
        }

        //---------------------

        //ARTIST------------
        public static void fillArtistDataGridView(DataGridView artistOnDataGridView)
        {
            MakeConnection();
            string sSQL = "SELECT * FROM ArtistTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "ArtistTable");
            artistOnDataGridView.DataSource = dataSet.Tables["ArtistTable"];
            CloseConnection();
        }

        public static void deleteArtist(DataGridView artistDataGridView, String id)
        {
            MakeConnection();
            string sSQL = "DELETE FROM ArtistTable WHERE ArtistID = '" + id + "'";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "ArtistTable");
            CloseConnection();
        }

        public static int getArtistMax()
        {
            int max;
            MakeConnection();
            string sSQL = "SELECT MAX(ArtistID) FROM ArtistTable";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sSQL, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "Table");
            max = Convert.ToInt16(dataSet.Tables["Table"].Rows[0][0]);
            CloseConnection();
            return max;
        }

        public static void insertArtists(int id, string realName, string country)
        {
            MakeConnection();
            string query = @"SELECT * FROM ArtistTable";
            string update = @"INSERT INTO ArtistTable(ArtistID,RealName,Country)values(@id,@realName,@country)";
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "ArtistTable");
            DataTable dataTable = dataSet.Tables["ArtistTable"];

            DataRow dataRow = dataTable.NewRow();
            dataRow["ArtistID"] = id;
            dataRow["RealName"] = realName;
            dataRow["Country"] = country;

            dataTable.Rows.Add(dataRow);

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@id", SqlDbType.Decimal, 9, "ArtistID");
            command.Parameters.Add("@realName", SqlDbType.VarChar, 50, "RealName");
            command.Parameters.Add("@country", SqlDbType.VarChar, 50, "Country");

            dataAdapter.InsertCommand = command;
            dataAdapter.Update(dataSet, "ArtistTable");
        }

        public static void editArtists(DataGridView albumsDataGridView, int id, string realName, string country)
        {
            MakeConnection();
            string query = @"SELECT * FROM ArtistTable";
            string update = @"UPDATE ArtistTable SET RealName = @realName, Country = @country WHERE ArtistID = @id";

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand(query, connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "ArtistTable");
            DataTable dataTable = dataSet.Tables["ArtistTable"];

            int row = Convert.ToInt32(albumsDataGridView.CurrentRow.Index);

            dataTable.Rows[row]["RealName"] = realName;
            dataTable.Rows[row]["Country"] = country;

            SqlCommand command = new SqlCommand(update, connection);
            command.Parameters.Add("@realName", SqlDbType.VarChar, 50, "RealName");
            command.Parameters.Add("@country", SqlDbType.VarChar, 50, "Country");
            SqlParameter parameter = command.Parameters.Add("@id", SqlDbType.Decimal, 9, "ArtistID");
            parameter.SourceVersion = DataRowVersion.Original;
            dataAdapter.UpdateCommand = command;
            dataAdapter.Update(dataSet, "ArtistTable");
        }
        //---------------------
    }
}